const express = require('express');
const router = express.Router();
const db = require('../db');
const multer = require('multer');
const moment = require('moment');

//分页获取文章
router.get('/list',(req,res)=>{

    let { pagenum, pagesize, cate_id, state } = req.query;

    let w = '';

    if (cate_id) {
        w += ` and cate_id=${cate_id} `;
    }

    if (state) {
        w += ` and state='${state}' `;
    }
    // 分页查询数据的SQL（该SQL用到了连表查询，并且使用了很多变量组合）
    let sql1 = `select a.id, a.title, a.state, a.pub_date, c.name cate_name from article a
    join category c on a.cate_id=c.id
    where author_id=${req.user.id} and is_delete=0 ${w}
    limit ${(pagenum - 1) * pagesize}, ${pagesize}`;

    // 查询总记录数的SQL，查询条件和前面查询数据的条件 必须要一致
    let sql2 = `select count(*) total from article a
    join category c on a.cate_id=c.id
    where author_id=${req.user.id} and is_delete=0 ${w}`;

    // 分别执行两条SQL（因为db查询数据库是异步方法，必须嵌套查询）
    db(sql1, (err, result1) => {
        if (err) throw err;
        db(sql2, (e, result2) => {
            if (e) throw e;
            res.send({
                status: 0,
                message: '获取文章列表数据成功',
                data: result1,
                total: result2[0].total
            });
        })
    });


})

//获取一篇文章
router.get('/:id',(req,res)=>{
    // console.log(req.params);
    db(`select * from article where id=${req.params.id}`,(err,result)=>{
        if(err) throw err;
        res.send({
            status:0,
            message:'获取文章详情成功',
            data:result[0]
        })
    })
    // res.send('11')
})

//删除一篇文章
router.get('/delete/:id',(req,res)=>{
    // console.log(req.user);
    db(`update article set is_delete=1 where id=${req.params.id} and author_id=${req.user.id}`,(err,result)=>{
        if(err) throw err;
        // console.log(result);
        if(result.affectedRows > 0){
            res.send({
                status:0,
                message:'删除文章成功'
            })
        }else{
            res.send({
                status:1,
                message:'删除文章失败'
            })
        }
        
    })
        //  res.send('11')
})

//添加文章
const upload = multer({dest:'upload/'})

router.post('/add',upload.single('cover_img'),(req,res)=>{
    // console.log(111);
    // res.send('11');
    let { title, content, cate_id, state } = req.body; 

    let pub_date = moment().format('YYYY-MM-DD HH:mm:ss');
    let cover_img = req.file.filename;
    let author_id = req.user.id;

    let sql = `insert into article set title='${title}', content='${content}', cate_id=${cate_id}, state='${state}', pub_date='${pub_date}', cover_img='${cover_img}', author_id=${author_id}`;
    
    db(sql, (err, result) => {
      if (err) throw err;
      if (result.affectedRows > 0) {
        res.send({ 
            status: 0, 
            message: '发布成功'
        })
      } else {
        res.send({ 
            status: 1,
            message: '发布失败' 
        })
      }
    })
})

//修改文章
router.post('/update', upload.single('cover_img'),(req,res)=>{
    // res.send("11")
    let { title, content, cate_id, state, id } = req.body; 
    // 其他字段（发布时间，不是修改时间，所以不需要改了，用户id也不需要改）
    let cover_img = req.file.filename;
    // console.log(obj);
    // return;
    let sql = `update article set title='${title}', content='${content}', cate_id=${cate_id}, state='${state}', cover_img='${cover_img}' where id=${id}`;
    db(sql, (err, result) => {
      if (err) throw err;
    //   console.log(result);
      if (result.affectedRows > 0) {
        res.send({
            status: 0,
            message: '修改文章成功' 
        })
      } else {
        res.send({ 
            status: 1, 
            message: '修改文章失败'
        })
      }
    })
})

module.exports = router;